using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;

namespace ApplicationTemplate
{
    class SQLClass
    {
        public static DataSet GetTableFields(BaseDAM dam, String tableName)
        {
            String sql = String.Format(@"
                SELECT name, user_type_id, is_identity, '0' is_pk
                FROM sys.all_columns 
                WHERE object_id in(
                    SELECT object_id 
                    FROM sys.all_objects 
                    WHERE name = '{0}')", tableName);

            DbCommand command = dam.GetSqlStringCommand(sql);

            DataSet ds = dam.ExecuteDataSet(command);


            DataSet pkDs = GetTablePK(dam, tableName);
            foreach (DataRow pkRow in pkDs.Tables[0].Rows)
            {
                String pkName = pkRow["COLUMN_NAME"].ToString();
                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    String name = row["name"].ToString();
                    if (name == pkName)
                    {
                        row["is_pk"] = "1";
                        break;
                    }
                }
            }

            return ds;
        }

        public static DataSet GetTablePK(BaseDAM dam, String tableName)
        {
            String sql = String.Format(@"sp_pkeys {0}", tableName);

            DbCommand command = dam.GetSqlStringCommand(sql);

            return dam.ExecuteDataSet(command);
        }


        public static String GetPkParams(DataSet ds)
        {
            String pk = String.Empty;

            foreach (DataRow row in ds.Tables[0].Rows)
            {
                if (row["is_pk"].ToString() == "1")
                {
                    String name = row["name"].ToString();
                    name = name.Substring(0, 1).ToLower() + name.Substring(1);
                    pk += String.Format("String {0}, ", name); ;
                }
            }

            return pk;
        }

        public static String GetPkParamsComment(DataSet ds)
        {
            String pk = String.Empty;

            foreach (DataRow row in ds.Tables[0].Rows)
            {
                if (row["is_pk"].ToString() == "1")
                {
                    String name = row["name"].ToString();
                    name = name.Substring(0, 1).ToLower() + name.Substring(1);
                    pk += String.Format("        /// <param name=\"{0}\">主键:{1}</param>" + Environment.NewLine, name, row["name"].ToString());
                }
            }

            pk = pk.TrimEnd(Environment.NewLine.ToCharArray());

            return pk;
        }
    }
}
